#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "


set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--关闭 mapjoin
set hive.auto.convert.join=false;


insert into table zxedu_dwb.dwb_relationship_detail
select fcr.id,
       fcr.create_date_time,
       fcr.update_date_time,
       fcr.deleted,
       fcr.customer_id,
       fcr.first_id,
       fcr.belonger,
       fcr.belonger_name,
       fcr.initial_belonger,
       fcr.distribution_handler,
       fcr.business_scrm_department_id,
       fcr.last_visit_time,
       fcr.next_visit_time,
       case
       when
       fcr.origin_type = 'NETSERVICE' or fcr.origin_type = 'PRESIGNUP' then 'online'
       when
       fcr.origin_type is not null and fcr.origin_type != 'NETSERVICE' and fcr.origin_type != 'PRESIGNUP' then 'offline'
       end as origin_type,
       fcr.itcast_school_id,
       dis.name as itcast_school_name,
       fcr.itcast_subject_id,
       disu.name as itcast_subject_name,
       fcr.intention_study_type,
       fcr.anticipat_signup_date,
       fcr.level,
       fcr.creator,
       fcr.current_creator,
       fcr.creator_name,
       fcr.origin_channel,
       fcr.comment,
       fcr.first_customer_clue_id,
       fcr.last_customer_clue_id,
       fcr.process_state,
       fcr.process_time,
       fcr.payment_state,
       fcr.payment_time,
       fcr.signup_state,
       fcr.signup_time,
       fcr.notice_state,
       fcr.notice_time,
       fcr.lock_state,
       fcr.lock_time,
       fcr.itcast_clazz_id,
       fcr.itcast_clazz_time,
       fcr.payment_url,
       fcr.payment_url_time,
       fcr.ems_student_id,
       fcr.delete_reason,
       fcr.deleter,
       fcr.deleter_name,
       fcr.delete_time,
       fcr.course_id,
       fcr.course_name,
       fcr.delete_comment,
       fcr.close_state,
       fcr.close_time,
       fcr.appeal_id,
       fcr.tenant,
       fcr.total_fee,
       fcr.belonged,
       fcr.belonged_time,
       fcr.belonger_time,
       fcr.transfer,
       fcr.transfer_time,
       fcr.follow_type,
       fcr.transfer_bxg_oa_account,
       fcr.transfer_bxg_belonger_name,
       fca.appeal_status as appeal_status,
       dsd.id as tdepart_id,
       dsd.name as tdepart_name,
       fcr.year_code,
       fcr.month_code,
       substring(fcr.create_date_time,1,10) as dt
from (select * from zxedu_dwd.fact_customer_relationship where end_date = '9999-99-99') fcr
left join zxedu_dwd.fact_customer_appeal fca on fcr.id = fca.customer_relationship_first_id and fca.end_date = '9999-99-99'
left join zxedu_dwd.dimension_employee de on fcr.creator = de.id and de.end_date = '9999-99-99'
left join zxedu_dwd.dimension_scrm_department dsd on de.tdepart_id = dsd.id and dsd.end_date = '9999-99-99'
left join zxedu_dwd.dimension_itcast_school dis on fcr.itcast_school_id = dis.id and dis.end_date = '9999-99-99'
left join zxedu_dwd.dimension_itcast_subject disu on fcr.itcast_subject_id = disu.id and disu.end_date = '9999-99-99';
"